package com.studentachievement.dao;

import com.studentachievement.beans.Score;
import com.studentachievement.beans.Student;
import com.studentachievement.utils.DatabaseUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {

    // 获取学生信息
    public static void aStudent() {
        Student student = null;
        String sql = "SELECT * FROM students";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection_();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                student = new Student();
                student.setStudentId(rs.getInt("student_id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setBirthDate(rs.getString("birth_date"));
                student.setMajor(rs.getString("major"));
                student.setPassword(rs.getString("password"));
                student.print();
            }
            rs.close();
            ps.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close_(conn, ps, rs);
        }
    }


    public static Student getStudentByUsernameAndPassword(String username, String password) {
        Student student = null;
        String sql = "SELECT * FROM students WHERE name = ? AND password = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);

            rs = ps.executeQuery();
            if (rs.next()) {
                student = new Student();
                student.setStudentId(rs.getInt("student_id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setBirthDate(rs.getString("birth_date"));
                student.setMajor(rs.getString("major"));
                student.setPassword(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close_(conn, ps, rs);
        }
        return student;
    }

     //获取学生成绩
    public static List<Score> getStudentScores(int studentId) {
        List<Score> scores = new ArrayList<>();
        String sql = "SELECT c.course_name, s.score, s.exam_date FROM scores s " +
                "JOIN courses c ON s.course_id = c.course_id WHERE s.student_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, studentId);
            rs = ps.executeQuery();
            while (rs.next()) {
                Score score = new Score();
                score.setCourseName(rs.getString("course_name"));
                score.setScore(rs.getDouble("score"));
                score.setExamDate(String.valueOf(rs.getDate("exam_date")));
                scores.add(score);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close_(conn, ps, rs);
        }
        return scores;
    }
}
